package com.wjw.utils.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * @desc 数据库工具类，所有数据库通用。
 * @author wjw
 * @date 2016年11月18日下午4:53:19
 */
public final class DBUtil {
	private DBUtil() {
	}
	
    /**
     * 日志对象
     */
    protected static Logger log = LoggerFactory.getLogger(DBUtil.class);

	/**
	 * 执行sql,返回boolean类型。注意:数据库连接未关闭，需要手动关闭
	 * @param conn  数据库连接
	 * @param sql  要执行的sql语句
	 * @return
	 */
	public static boolean executeSQL(Connection conn, String sql) {
		log.info("要执行的sql:"+sql);
		PreparedStatement pst = null;
		boolean flg=false;
		try {
			if (conn == null || conn.isClosed() || sql == null
					|| "".equals(sql.trim())) {
				return flg;
			}
			pst = conn.prepareStatement(sql);
			pst.executeUpdate();
			conn.commit();
			flg=true;
		} catch (SQLException e) {
			e.printStackTrace();
			log.info("执行sql出错:"+sql);
			log.info(e.getMessage());
			flg=false;
		} finally {
			close(null,null,pst);
		}
		return flg;
	}

	/**
	 * 判断表或视图是否存在。注意，返回结果后连接Connection并没有关闭，需手动关闭
	 * @param conn 连接
	 * @param name 表或视图名
	 * @return
	 */
	public static boolean isExists(Connection conn, String name) {
		boolean flg = false;
		DatabaseMetaData meta;
		ResultSet rs = null;
		try {
			meta = conn.getMetaData();
			rs = meta.getTables(null, null, name, null);
			if (rs.next()) {
				flg = true;
			}
		} catch (SQLException e) {
			log.info("查询错误:查询异常");
			log.info(e.getMessage());
			e.printStackTrace();
		} finally {
			close(null, rs, null);
		}
		return flg;
	}

	/**
	 * 删除表或视图,注意:连接未关闭，需要手动关闭
	 * @param conn 数据库连接
	 * @param name
	 * @param type
	 * @return
	 */
	public static boolean dropTableOrView(Connection conn, String name,String type) {
		boolean flg = true;
		String sql = "drop " + type + " " + name;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			log.info(sql);
			flg = ps.execute();
			conn.commit();
		} catch (SQLException e) {
			flg = false;
			log.info("删除错误:表或视图不存在");
			e.printStackTrace();
		} finally {
			close(null, null, ps);
		}
		return flg;
	}

	/**
	 * 获取表主键(存放在List中，如果表没有主键，则List大小为0，不是null)，连接未关闭
	 * @param conn
	 * @param tableName
	 * @return
	 */
	public static List<String> getTablePK(Connection conn,String tableName){
		List<String> pks=new ArrayList<String>(5);
		DatabaseMetaData metaData=null;
		ResultSet rs=null;
		try {
			metaData =conn.getMetaData();
			rs = metaData.getPrimaryKeys(conn.getCatalog(),null,tableName);
			while(rs.next()){
				pks.add(rs.getString("COLUMN_NAME"));
			}
		} catch (SQLException e) {
			log.info("获取主键异常:"+e.getMessage());
			e.printStackTrace();
		}finally{
			close(null,rs,null);
		}
		return pks;
	}
	
	/**
	 * 关闭数据库连接
	 * @param conn
	 * @param rs
	 * @param st
	 */
	public static void close(Connection conn,ResultSet rs, Statement st) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 查询数据库数量,数据库连接未关闭
	 * @param conn
	 * @param tableName
	 * @param params 条件块
	 * @return
	 */
	public static int getDataCount(Connection conn,String tableName,String params){
		PreparedStatement ps = null;
		ResultSet rs=null;
		int count=0;
		String sql="SELECT COUNT(*) as totalCount FROM "+tableName+" "+params;
		log.info("查询数量:"+sql);
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			count=rs.getInt("totalCount");
		} catch (SQLException e) {
			log.info("查询数量异常:"+e.getMessage());
			e.printStackTrace();
		}finally{
			close(null, rs, ps);			
		}
		return count;
	}
		
	/**
	 * 从表中删除指定的数据(delete).注意:数据库连接未关闭
	 * @param conn
	 * @param tableName
	 * @param params 条件块
	 */
	public static void deleteData(Connection conn,String tableName,String params){
		PreparedStatement ps = null;
		int count=0;
		String sql="DELETE FROM "+tableName+" "+params;
		log.info("删除表"+tableName+"中的数据:"+sql);
		try {
			ps=conn.prepareStatement(sql);
			count=ps.executeUpdate();
			log.info("从表"+tableName+"中删除了"+count+"条数据");
		} catch (SQLException e) {
			log.info("删除数据异常:"+e.getMessage());
			e.printStackTrace();
		}finally{
			close(null, null, ps);			
		}
	}
		
	/**
	 * 将视图中的数据插入到表中.注意:数据库连接未关闭
	 * @param conn
	 * @param tableName 表
	 * @param valueName 视图表
	 * @param params 条件块
	 */
	public static void insertData(Connection conn,String tableName,String valueName,String params){
		PreparedStatement ps = null;
		int count=0;
		String sql="INSERT INTO "+tableName+" SELECT * FROM "+valueName+" "+ params;
		log.info("要插入的sql语句:"+sql);
		try {
			ps=conn.prepareStatement(sql);
			count=ps.executeUpdate();
			log.info("从视图中插入了"+count+"条数据");
		} catch (SQLException e) {
			log.info("插入数据异常:"+e.getMessage());
			e.printStackTrace();
		}finally{
			close(null, null, ps);			
		}
	}
}
